﻿using RTW.DAL;
using RTW.OA.Models;
using System.Collections.Generic;
using System.Web.Mvc;

namespace RTW.OA.Servers
{
    public class GenerateServers
    {
        private readonly IRepository<sys_menu> GenerateRepository;

        public GenerateServers()
        {
            GenerateRepository = new DapperRepository<sys_menu>();
        }

        internal List<SelectListItem> GetTables()
        {
            List<SelectListItem> tables = new List<SelectListItem>();
            var dstable = GenerateRepository.SqlWithParamsDynamic("select a.name Value,concat(a.name,'-----',convert(nvarchar(max),b.value)) Text from sys.tables  a inner join  sys.extended_properties b on a.object_id=b.major_id and b.minor_id=0");
            tables.Clear();

            foreach (var dr in dstable)
            {
                string Id = dr.Value.ToString();
                string Title = dr.Text.ToString().Trim();
                tables.Add(new SelectListItem { Value = Id, Text = Title });
            }

            return tables;
        }

        internal object GetColumns(string tablename)
        {
            var sql = @"SELECT
                            SORT   = a.colorder,
                            COLNAME     = a.name,
                            AUTOID       = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
                            [KEY]       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                                             SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
                            [TYPE]       = b.name,
                            LENS       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
                            [FLOAT]   = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
                            [ISNULL]     = case when a.isnullable=1 then '√'else '' end,
                            [DEFAULT]     = isnull(e.text,''),
                            [DESC]   = isnull(g.[value],''),
                            CONTROL='',
                            VALIDATE='',
                            [STATUS]='true',
                            [LIST]='true',
                            [EDIT]='true',
                            [QUERY]='true'
                        FROM
                            syscolumns a
                        left join
                            systypes b
                        on
                            a.xusertype=b.xusertype
                        inner join
                            sysobjects d
                        on
                            a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
                        left join
                            syscomments e
                        on
                            a.cdefault=e.id
                        left join
                        sys.extended_properties   g
                        on
                            a.id=G.major_id and a.colid=g.minor_id
                        where
                            d.name=@TableName
                        order by
                            a.id,a.colorder";
            var coltable = GenerateRepository.SqlWithParamsDynamic(sql, new { TableName = tablename });
            return AutoMapper.Mapper.Map<List<ColModel>>(coltable);
        }
    }
}